package FM2_15577.back_end;

import org.json.JSONArray;
import org.json.JSONObject;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.net.Socket;
import java.time.LocalDate;
import java.time.LocalTime;
import java.util.Random;
import java.sql.*;

public class ServerHandler implements Runnable {
    private Socket s;
    private JSONObject request;
    private JSONObject ret;
    private Connector conn;

    public ServerHandler(Socket s) {
        this.s = s;
    }

    public void processRegisterAccount() throws Exception {
        try {
            Random r = new Random(LocalTime.now().toNanoOfDay()+LocalDate.now().toEpochDay());
            boolean flag = true;
            while (flag) {
                long aid = Math.abs(r.nextLong()) % (long)(9e9) + (long)(1e9);
                System.out.println("aid: "+aid);
                conn.melock();
                conn.execute(String.format("select * from AccountInfo where aid=%d", aid));
                ResultSet rs = conn.getResultSet();
                int countRow = 0;
                while (rs.next()) {
                    countRow++;
                }
                if (countRow == 0) {
                    conn.execute(String.format("INSERT INTO AccountInfo VALUES (%d, '%s', '%s', %d, %d, '%c', '%s', 2000, FALSE, %d)", aid, this.request.getString("name"), this.request.getString("cipher"), this.request.getLong("id"), this.request.getLong("phone"), (char)(this.request.getInt("sex")), this.request.get("dob"), this.request.getInt("createYear")));
                    flag = false;
                    this.ret.put("aid", aid);
                }
            }
            conn.meunlock();
        } catch (Exception e) {
            conn.meunlock();
            throw e;
        }
    }

    public void processLoginAccount() throws Exception {
        try {
            long aid = this.request.getLong("aid");
            conn.melock();
            conn.execute("update AccountInfo set closed=TRUE where ADDDATE(dob, INTERVAL 70 YEAR) <= CURDATE()");
            conn.execute(String.format("select cipher, closed from AccountInfo where aid=%d", aid));
            ResultSet rs = conn.getResultSet();
            boolean closed;
            String cipherString;
            if (rs.next()) {
                closed = rs.getBoolean("closed");
                if (closed) {
                    throw new Exception("Account is closed!");
                }
                cipherString = rs.getString("cipher");
                if (!cipherString.equals(this.request.getString("cipher"))) {
                    throw new Exception("Wrong password!");
                }
            }
            else {
                throw new Exception("Account does not exist!");
            }
            conn.meunlock();
        } catch (Exception e) {
            conn.meunlock();
            throw e;
        }
    }

    public void processRemoveAccount() throws Exception {
        try {
            long aid = this.request.getLong("aid");
            conn.melock();
            conn.execute(String.format("select closed from AccountInfo where aid=%d", aid));
            ResultSet rs = conn.getResultSet();
            if (rs.next()) {
                if (rs.getBoolean("closed")) {
                    throw new Exception("Account is closed!");
                }
                conn.execute(String.format("update AccountInfo set closed=TRUE where aid=%d", aid));
            }
            else {
                throw new Exception("Account does not exist!");
            }
            conn.meunlock();
        } catch (Exception e) {
            conn.meunlock();
            throw e;
        }
    }

    public void processRegisterAdminAccount() throws Exception {
        try {
            String nameString = this.request.getString("name");
            System.out.println("name: "+nameString);
            conn.melockadmin();
            conn.execute(String.format("select * from AdminInfo where name='%s'", nameString));
            ResultSet rs = conn.getResultSet();
            int countRow = 0;
            while (rs.next()) {
                countRow++;
            }
            if (countRow == 0) {
                conn.execute(String.format("INSERT INTO AdminInfo (name, cipher) VALUES ('%s', '%s')", nameString, this.request.getString("cipher")));
                this.ret.put("name", nameString);
            }
            else {
                throw new Exception("Admin name exists!");
            }
            conn.meunlock();
        } catch (Exception e) {
            conn.meunlock();
            throw e;
        }
    }

    public void processLoginAdminAccount() throws Exception {
        try {
            String name = this.request.getString("name");
            conn.melock();
            conn.execute("update AccountInfo set closed=TRUE where ADDDATE(dob, INTERVAL 70 YEAR) <= CURDATE()");
            conn.meunlock();
            conn.melockadmin();
            conn.execute(String.format("select cipher from AdminInfo where name='%s'", name));
            ResultSet rs = conn.getResultSet();
            String cipherString;
            if (rs.next()) {
                cipherString = rs.getString("cipher");
                if (!cipherString.equals(this.request.getString("cipher"))) {
                    throw new Exception("Wrong password!");
                }
            }
            else {
                throw new Exception("Account does not exist!");
            }
            conn.meunlock();
        } catch (Exception e) {
            conn.meunlock();
            throw e;
        }
    }

    public void processGetBalance() throws Exception {
        try {
            long aid = this.request.getLong("aid");
            conn.melock();
            conn.execute(String.format("select balance from AccountInfo where aid=%d", aid));
            ResultSet rs = conn.getResultSet();
            String balance;
            if (rs.next()) {
                balance = rs.getString("balance");
                this.ret.put("balance", balance);
            }
            else {
                throw new Exception("Account does not exist!");
            }
            conn.meunlock();
        } catch (Exception e) {
            conn.meunlock();
            throw e;
        }
    }

    public void processDeposit() throws Exception {
        try {
            long aid = this.request.getLong("aid");
            String amount = this.request.getString("amount");
            conn.melock();
            conn.execute(String.format("update AccountInfo set balance=balance+%s where aid=%d", amount, aid));
            conn.execute(String.format("select balance from AccountInfo where aid=%d", aid));
            ResultSet rs = conn.getResultSet();
            String balance;
            if (rs.next()) {
                balance = rs.getString("balance");
                this.ret.put("balance", balance);
            }
            else {
                throw new Exception("Account does not exist!");
            }
            conn.meunlock();
        } catch (Exception e) {
            conn.meunlock();
            throw e;
        }
    }

    public void processWithdraw() throws Exception {
        try {
            long aid = this.request.getLong("aid");
            String amount = this.request.getString("amount");
            conn.melock();
            conn.execute(String.format("update AccountInfo set balance=balance-%s where aid=%d", amount, aid));
            conn.execute(String.format("select balance from AccountInfo where aid=%d", aid));
            ResultSet rs = conn.getResultSet();
            String balance;
            if (rs.next()) {
                balance = rs.getString("balance");
                this.ret.put("balance", balance);
            }
            else {
                throw new Exception("Account does not exist!");
            }
            conn.meunlock();
        } catch (Exception e) {
            conn.meunlock();
            throw e;
        }
    }

    public void processTransfer() throws Exception {
        try {
            long aid = this.request.getLong("aid");
            long taid = this.request.getLong("taid");
            String amount = this.request.getString("amount");
            conn.melock();
            conn.execute(String.format("select closed from AccountInfo where aid=%d", taid));
            ResultSet rs = conn.getResultSet();
            boolean closed;
            if (rs.next()) {
                closed = rs.getBoolean("closed");
                if (closed) {
                    throw new Exception("Target account closed!");
                }
            }
            else {
                throw new Exception("Target account does not exist!");
            }
            conn.execute(String.format("update AccountInfo set balance=balance-%s where aid=%d", amount, aid));
            conn.execute(String.format("update AccountInfo set balance=balance+%s where aid=%d", amount, taid));
            conn.execute(String.format("select balance from AccountInfo where aid=%d", aid));
            rs = conn.getResultSet();
            String balance;
            if (rs.next()) {
                balance = rs.getString("balance");
                this.ret.put("balance", balance);
            }
            else {
                throw new Exception("Account does not exist!");
            }
            conn.meunlock();
        } catch (Exception e) {
            conn.meunlock();
            throw e;
        }
    }

    public void processUpdateInfo() throws Exception {
        try {
            long aid = this.request.getLong("aid");
            String name = this.request.getString("name");
            String cipher = this.request.getString("cipher");
            long phone = this.request.getLong("phone");
            char sex = (char)this.request.getInt("sex");
            conn.melock();
            conn.execute(String.format("update AccountInfo set name='%s', cipher='%s', phone=%d, sex='%c' where aid=%d", name, cipher, phone, sex, aid));
            conn.meunlock();
        } catch (Exception e) {
            conn.meunlock();
            throw e;
        }
    }

    public void processExportAccountInfo() throws Exception {
        try {
            conn.melock();
            conn.execute(String.format("select * from AccountInfo"));
            ResultSet rs = conn.getResultSet();
            long cntAccount = 0;
            JSONArray accountsArray = new JSONArray();
            while (rs.next()) {
                ++cntAccount;
                JSONObject account = new JSONObject();
                account.put("aid", rs.getString("aid"));
                account.put("name", rs.getString("name"));
                account.put("cipher", rs.getString("cipher"));
                account.put("id", rs.getString("id"));
                account.put("phone", rs.getString("phone"));
                account.put("sex", rs.getString("sex"));
                account.put("dob", rs.getString("dob"));
                account.put("balance", rs.getString("balance"));
                account.put("closed", rs.getString("closed"));
                account.put("openyear", rs.getString("openyear"));
                accountsArray.put(account);
            }
            this.ret.put("cntAccount", cntAccount);
            this.ret.put("accounts", accountsArray);
            conn.meunlock();
        } catch (Exception e) {
            conn.meunlock();
            throw e;
        }
    }

    public void processExportReport() throws Exception {
        try {
            conn.melock();
            ResultSet rs;
            conn.execute(String.format("select count(*) as count from AccountInfo"));
            rs = conn.getResultSet();
            if (rs.next()) {
                String totalCntAccounts = rs.getString("count");
                this.ret.put("totalCntAccounts", totalCntAccounts);
            }
            else {
                throw new Exception("Failed to get total count of accounts!");
            }

            conn.execute(String.format("select count(*) as count from AccountInfo where openyear=%d", LocalDate.now().getYear()));
            rs = conn.getResultSet();
            if (rs.next()) {
                String newAccountsThisYear = rs.getString("count");
                this.ret.put("newAccountsThisYear", newAccountsThisYear);
            }
            else {
                throw new Exception("Failed to get count of new accounts in this year!");
            }

            conn.execute(String.format("select sum(balance) as sum from AccountInfo"));
            rs = conn.getResultSet();
            if (rs.next()) {
                String totalBalance = rs.getString("sum");
                this.ret.put("totalBalance", totalBalance);
            }
            else {
                throw new Exception("Failed to get sum of balance!");
            }
            conn.meunlock();
        } catch (Exception e) {
            conn.meunlock();
            throw e;
        }
    }

    public void run() {
        try {
            System.out.println(String.format("Connection established from: %s:%d", s.getInetAddress(), s.getPort())); // log info
            BufferedReader br = new BufferedReader(new InputStreamReader(s.getInputStream())); // reader
            BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(s.getOutputStream())); // writer

            String reqString = null; // request string
            String result = ""; // 返回结果
            String reason = ""; // 返回原因
            reqString = br.readLine();
            if (reqString.equals("")) {
                System.out.println("Empty request!");
                return;
            }

            System.out.println(String.format("%s:%d : %s", s.getInetAddress(), s.getPort(), reqString));
            this.request = new JSONObject(reqString);
            if (this.request.has("vk") && this.request.getString("vk").equals("f209e2885166be011e829e93fe0c2243ecb20a722c44b9209eb092f1bf01eace")) {
                // System.err.println("Valid request.");
                this.conn = new Connector();
                this.conn.start(); // start connector
                ret = new JSONObject();

                if (this.request.getString("type").equals("registerAccount")) {
                    try {
                        System.out.println("Create Account: ");
                        this.processRegisterAccount();
                        result = "success";
                        reason = "created";
                    } catch (Exception e) {
                        result = "failed";
                        reason = e.getMessage();
                    }
                }
                else if (this.request.getString("type").equals("loginAccount")) {
                    try {
                        System.out.println("Login Account: ");
                        this.processLoginAccount();
                        result = "success";
                        reason = "login successfully";
                    } catch (Exception e) {
                        result = "failed";
                        reason = e.getMessage();
                    }
                }
                else if (this.request.getString("type").equals("removeAccount")) {
                    try {
                        System.out.println("Remove Account: ");
                        this.processRemoveAccount();
                        result = "success";
                        reason = "remove successfully";
                    } catch (Exception e) {
                        result = "failed";
                        reason = e.getMessage();
                    }
                }
                else if (this.request.getString("type").equals("loginAdminAccount")) {
                    try {
                        System.out.println("Login Admin Account: ");
                        this.processLoginAdminAccount();
                        result = "success";
                        reason = "Login admin successfully";
                    } catch (Exception e) {
                        result = "failed";
                        reason = e.getMessage();
                    }
                }
                else if (this.request.getString("type").equals("registerAdminAccount")) {
                    try {
                        System.out.println("Register Admin Account: ");
                        this.processRegisterAdminAccount();
                        result = "success";
                        reason = "Register admin successfully";
                    } catch (Exception e) {
                        result = "failed";
                        reason = e.getMessage();
                    }
                }
                else if (this.request.getString("type").equals("getBalance")) {
                    try {
                        System.out.println("Get Balance: ");
                        this.processGetBalance();
                        result = "success";
                        reason = "Get balance successfully";
                    } catch (Exception e) {
                        result = "failed";
                        reason = e.getMessage();
                    }
                }
                else if (this.request.getString("type").equals("deposit")) {
                    try {
                        System.out.println("Deposit: ");
                        this.processDeposit();
                        result = "success";
                        reason = "Deposit successfully";
                    } catch (Exception e) {
                        result = "failed";
                        reason = e.getMessage();
                    }
                }
                else if (this.request.getString("type").equals("withdraw")) {
                    try {
                        System.out.println("Deposit: ");
                        this.processWithdraw();
                        result = "success";
                        reason = "Withdraw successfully";
                    } catch (Exception e) {
                        result = "failed";
                        reason = e.getMessage();
                    }
                }
                else if (this.request.getString("type").equals("transfer")) {
                    try {
                        System.out.println("Transfer: ");
                        this.processTransfer();
                        result = "success";
                        reason = "Transfer successfully";
                    } catch (Exception e) {
                        result = "failed";
                        reason = e.getMessage();
                    }
                }
                else if (this.request.getString("type").equals("updateInfo")) {
                    try {
                        System.out.println("Update information: ");
                        this.processUpdateInfo();
                        result = "success";
                        reason = "Update information successfully";
                    } catch (Exception e) {
                        result = "failed";
                        reason = e.getMessage();
                    }
                }
                else if (this.request.getString("type").equals("exportAccountInfo")) {
                    try {
                        System.out.println("Export account info: ");
                        this.processExportAccountInfo();
                        result = "success";
                        reason = "Export account info successfully";
                    } catch (Exception e) {
                        result = "failed";
                        reason = e.getMessage();
                    }
                }
                else if (this.request.getString("type").equals("exportReport")) {
                    try {
                        System.out.println("Export report: ");
                        this.processExportReport();
                        result = "success";
                        reason = "Export report successfully";
                    } catch (Exception e) {
                        result = "failed";
                        reason = e.getMessage();
                    }
                }
            
                ret.put("recv date", LocalDate.now().toString());
                ret.put("recv time", LocalTime.now().toString());
                ret.put("result", result);
                ret.put("reason", reason);
                ret.put("vk", "e2439ef9727e3faf8b20df35465d49a51200f2c3c64c18fa81e9ffedc361afcf");
                bw.write(ret.toString());
                bw.newLine();
                bw.flush();
                System.out.println(String.format("reply [%s:%d] : %s", s.getInetAddress(), s.getPort(), ret.toString()));
            }
            else {
                System.out.println(String.format("%s:%d : Invalid request!", s.getInetAddress(), s.getPort()));
            }

            Thread.sleep(5000); // debug, 测试多线程
            br.close();
            bw.close();
            System.out.println(String.format("Connection closed: %s:%d", s.getInetAddress(), s.getPort()));
            s.close();
        } catch (Exception e) {
            System.out.println("Exception: ");
            System.out.println(e.getMessage());
            e.printStackTrace();
            System.out.println(String.format("Connection closed with exception: %s:%d", s.getInetAddress(), s.getPort()));
            try {
                s.close();
            } catch (Exception ex) { }
        }
    }
}
